package com.itmk.course.dao.impl;

import com.itmk.common.BaseDao;
import com.itmk.course.dao.CourseDao;
import com.itmk.course.entity.Course;
import com.itmk.course.entity.CourseParm;
import com.itmk.course.entity.CourseVo;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class CourseDaoImpl extends BaseDao implements CourseDao {
    @Override
    public void add(Course course) {
        //1、定义sql
        String sql = "insert into course(course_id,major_id,course_name,course_num,course_desc,course_type,course_count," +
                "course_point,address) " +
                "values(?,?,?,?,?,?,?,?,?)";
        //2、定义handler
        BeanHandler<Course> handler = new BeanHandler<>(Course.class);
        //3、定义参数
        Object[] parms = {
                course.getCourseId(),
                course.getMajorId(),
                course.getCourseName(),
                course.getCourseNum(),
                course.getCourseDesc(),
                course.getCourseType(),
                course.getCourseCount(),
                course.getCoursePoint(),
                course.getAddress()
        };
        try {
            query.insert(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Course> list(CourseParm courseParm) {
        //处理关键字查询
        if(StringUtils.isNotEmpty(courseParm.getCourseName())){
            //1、定义sql
            StringBuilder sb = new StringBuilder("select * from (select course.* , ROWNUM rm from course) ");
            sb.append(" where course_name like '%" + courseParm.getCourseName() + "%'");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Course> handler = new BeanListHandler<>(Course.class,processor);
            try {
                return query.query(sql,handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else {
            StringBuilder sb = new StringBuilder("select * from (select course.* , ROWNUM rm from course) ");
            sb.append("  where rm >= ? and rm <= ?");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Course> handler = new BeanListHandler<>(Course.class,processor);
            //3、参数
            Object[] parms = {
                    (courseParm.getPage()-1)*courseParm.getLimit(),
                    (courseParm.getLimit()*courseParm.getPage())
            };
            try {
                return query.query(sql,handler,parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public int getCount(CourseParm courseParm) {
         //1、定义sql
        StringBuilder sb = new StringBuilder("select count(course_id) from course ");
        //处理关键字查询
        if(StringUtils.isNotEmpty(courseParm.getCourseName())){
            sb.append(" where course_name like '%" + courseParm.getCourseName() + "%'");
        }
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();
        try {
            return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Course getById(String courseId) {
        //1、定义sql
        String sql = "select * from course where course_id=?";
        //2、定义handler
        BeanHandler<Course> handler = new BeanHandler<>(Course.class,processor);
        try {
            return query.query(sql,handler,courseId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void update(Course course) {
        //1、定义SQL
        String sql = "update course set major_id=?,course_name=?,course_num=?,course_desc=?,course_type=?,course_count=?," +
                "course_point=?,address=?  where course_id=?";
        //2、定义参数
        Object[] parms = {
                course.getMajorId(),
                course.getCourseName(),
                course.getCourseNum(),
                course.getCourseDesc(),
                course.getCourseType(),
                course.getCourseCount(),
                course.getCoursePoint(),
                course.getAddress(),
                course.getCourseId()
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void delete(String courseId) {
        //1、定义sql
        String sql = "delete from course where course_id=?";
        try {
            query.update(sql,courseId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<CourseVo> getList(CourseParm courseParm) {

        //处理关键字查询
        if(StringUtils.isNotEmpty(courseParm.getCourseName())){
            //1、定义sql
            StringBuilder sb = new StringBuilder("select cs.*,t.teacher_name,t.teacher_id from course  cs left join " +
                    "course_teacher " +
                    " ct  on cs.course_id = ct.course_id left join teacher  t on t.teacher_id = ct.teacher_id ");
            sb.append(" where cs.course_name like '%" + courseParm.getCourseName() + "%'");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<CourseVo> handler = new BeanListHandler<>(CourseVo.class,processor);
            try {
                return query.query(sql,handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else {
            //1、定义sql
            StringBuilder sb = new StringBuilder("select  * from (select cs.*,t.teacher_name,t.teacher_id ,ROWNUM rm from course  cs\n" +
                    "left join course_teacher ct  on cs.course_id = ct.course_id\n" +
                    "left join teacher  t on t.teacher_id = ct.teacher_id) ");
            sb.append("  where rm >= ? and rm <= ?");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<CourseVo> handler = new BeanListHandler<>(CourseVo.class,processor);
            //3、参数
            Object[] parms = {
                    (courseParm.getPage()-1)*courseParm.getLimit(),
                    (courseParm.getPage()*courseParm.getLimit())
            };
            try {
                return query.query(sql,handler,parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public int getCourseCount(CourseParm courseParm) {
        //1、定义sql
        StringBuilder sb = new StringBuilder("select count(course_id) from course");
        //处理关键字查询
        if(StringUtils.isNotEmpty(courseParm.getCourseName())){
            sb.append(" where cs.course_name like '%" + courseParm.getCourseName() + "%'");
        }
        String sql = sb.toString();

        //2、定义handler
        ScalarHandler handler = new ScalarHandler();

        try {
            String s = query.query(sql, handler).toString();
            return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
